package cn.zyy.dao;

import cn.zyy.entity.Student;
import cn.zyy.utils.JDBCUtil;

import java.sql.*;
import java.util.HashMap;

/**
 * studentDao类  用于持久化
 */
public class StudentDaoImpl implements StudentDao {
    //添加学生
    public int addStudent(Student student){
        Connection conn = JDBCUtil.getConnection();
        String sql = "insert into student(stu_id,stu_name,class_id) values(?,?,?)";
        PreparedStatement pstmt = null;
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setLong(1,student.getStuId());
            pstmt.setString(2,student.getStuName());
            pstmt.setLong(3,student.getClassId());
            int line = pstmt.executeUpdate();
            return line;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUtil.closeAll(null,pstmt,conn);
        }
        return 0;
    }
    //根据id删除学生
    public int deleteStudent(int id) {
        Connection conn = JDBCUtil.getConnection();
        String sql = "delete from student where stu_id = ?";
        PreparedStatement pstmt = null;
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setLong(1,id);
            int line = pstmt.executeUpdate();
            return line;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUtil.closeAll(null,pstmt,conn);
        }
        return 0;
    }
    //根据学生id和班级名称给学生分班（根据班级名称查询班级ID，然后根据学生id，更新学生的班级信息）
    public Integer classification(String className) {
        Connection conn = JDBCUtil.getConnection();
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        String sql = "select class_id from class where class_name = ?";
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1,className);
            rs = pstmt.executeQuery();
            int classId = 0;
            while(rs.next()){
                classId = rs.getInt("class_id");
            }
            return classId;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUtil.closeAll(rs,pstmt,conn);
        }
        return null;
    }
    public int classification(int stuId, Integer classId) {
        Connection conn = JDBCUtil.getConnection();
        PreparedStatement pstmt = null;
        String sql = "update student set class_id = ? where stu_id = ?";
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setLong(1,classId);
            pstmt.setLong(2,stuId);
            int line = pstmt.executeUpdate();
            return line;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUtil.closeAll(null,pstmt,conn);
        }
        return 0;
    }
    //查询所有学生信息，要求显示对应班级名称
    public HashMap<String,String> selectStudent() {
        Connection conn = JDBCUtil.getConnection();
        Statement stmt = null;
        ResultSet rs = null;
        try {
            stmt = conn.createStatement();
            String sql = "select s.stu_name,c.class_name from student s LEFT JOIN class c ON s.class_id = c.class_id";
            rs = stmt.executeQuery(sql);
            HashMap<String, String> student = new HashMap<String, String>();
            String stuName = "";
            String className = "";
            while(rs.next()) {
                stuName = rs.getString("stu_name");
                className = rs.getString("class_name");
                student.put(stuName,className);
            }
            return student;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
          JDBCUtil.closeAll(rs,stmt,conn);
        }
        return null;
    }

}
